package com.eastcom.collection.script

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.net.InetAddress.Cache;
import java.sql.Date;
import java.sql.PreparedStatement
import java.sql.SQLException
import java.sql.Timestamp
import java.sql.Types;
import java.text.SimpleDateFormat
import java.util.ArrayList;
import java.util.List;
import java.util.Map.Entry
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.xml.datatype.XMLGregorianCalendar;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType
import javax.xml.bind.annotation.XmlAttribute;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlValue;

import com.eastcom.collection.tools.db.DbEngine
import com.eastcom.groovy.provider.*
import com.eastcom.groovy.tools.mail.*
import com.sun.java.util.jar.pack.Instruction.Switch;
import com.sun.org.apache.bcel.internal.generic.NEW;
import com.sun.org.apache.bcel.internal.generic.RETURN;
import com.sun.org.apache.bcel.internal.util.ClassPath.Dir;
import com.eastcom.collection.provider.*
import com.eastcom.collection.config.PmFile
import com.eastcom.collection.context.*
import com.eastcom.collection.tools.file.*

import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory;
import org.apache.commons.net.ftp.FTPFile
import org.apache.commons.net.ftp.FTPFileFilter
import org.apache.tools.ant.types.FileList;
import org.springframework.dao.DataAccessException
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback
import org.springframework.jdbc.support.lob.LobCreator
import org.springframework.jdbc.support.lob.OracleLobHandler
import org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Component
/**
 * 任务类，可配置
 * @author yuanyue
 *
 */
class SimplePmDatabaseTaskDirector_SGSNFunction {
	/**
	 * 使用jaxb解析xml再入库
	 */
	/************************配置属性，可以修改，请尽量使用英文防止中文编码问题***************************/
	List<String> xmlFilePath
	String localRootPath
	final static String dstdbBean = "oracle_39_nhm"
	static String tableName = "SGSN_PM_V3_SGSNFUNCTION"
	static String selectSql0 =
	"SELECT t1.COLUMN_NAME COLUMN_NAME,\n"+
	"t2.COMMENTS COMMENTS\n"+
	"FROM user_tab_columns t1,\n"+
	"user_col_comments t2\n"+
	"WHERE t1.TABLE_NAME= ?\n"+
	"AND t1.TABLE_NAME  =t2.TABLE_NAME\n"+
	"AND t1.COLUMN_NAME =t2.COLUMN_NAME"
	static String selectSql1 =
	"select GROUP_ID\n"+
	"from nhm_pm_unit_collect_kpi_group A\n"+
	"where A.Begintime >= ?\n"+
	"and A.ENDTIME < ?"
	static String selectSql2 =
	"select \n"+
	"A.BEGINTIME BEGINTIME,\n"+
	"A.ENDTIME ENDTIME,\n"+
	"A.INFOMODELREFERENCED INFOMODELREFERENCED,\n"+
	"A.DNPREFIX DNPREFIX,\n"+
	"A.SENDERNAME SENDERNAME,\n"+
	"A.VENDORNAME VENDORNAME,\n"+
	"A.JOBID JOBID,\n"+
	"A.OBJECTTYPE OBJECTTYPE,\n"+
	"B.PM_NAME PM_NAME,\n"+
	"B.PM_DN PM_DN,\n"+
	"B.PM_USERLABEL PM_USERLABEL,\n"+
	"B.PM_VALUE PM_VALUE\n"+
	"from nhm_pm_unit_collect_kpi_group A, nhm_pm_unit_collect_kpi_detail B\n"+
	"where A.GROUP_ID = B.GROUP_ID\n"+
	"and A.GROUP_ID = ?\n"+
	"and A.OBJECTTYPE = 'SgsnFunction'"
	/***************************可配置部分结束***********************************************/

















	ThreadPoolTaskExecutor threadPoolTaskExecutor
	static Map<String,String> dstDbFeildAndComment = new HashMap<String, String>()
	static Log logger = LogFactory.getLog(SimplePmDatabaseTaskDirector_SGSNFunction.class)

	/*********************initJob和doJob是要被程序调用的方法**************************/
	public static boolean initJob(String jobId){
		DbEngine dbEngine = SpringContextProvider.getBean("dbEngine")
		List<Map<String, Object>> result = null;
		int[] types = new int[1]
		types[0] = Types.VARCHAR
		Object[] params = new Object[1]
		params[0] = tableName
		try {
			result = dbEngine.getJdbcUtil().queryForList(selectSql0, params, types);
			for (int i = 0; i < result.size(); i++) {
				Map<String, Object> res = result.get(i)
				String COLUMN_NAME = res.get("COLUMN_NAME")
				String COMMENTS = res.get("COMMENTS") == null ? "" : res.get("COMMENTS")
				dstDbFeildAndComment.put(COLUMN_NAME, COMMENTS)
				println COLUMN_NAME+"--"+COMMENTS
			}
		} catch (Exception e) {
			// TODO: handle exception
			logger.error(this,e)
		}
	}
	public boolean doJob(String jobId){
		List<Map<String, Object>> result = null;
		List<String> groupIdList = new ArrayList<String>()
		DbEngine dbEngine = SpringContextProvider.getBean("dbEngine")

		int[] types = new int[2]
		types[0] = Types.TIMESTAMP
		types[1] = Types.TIMESTAMP
		Object[] params = new Object[2]
		Calendar calend = Calendar.getInstance()
		calend.setTime(new java.util.Date())
		params[1] = new Timestamp(calend.getTime().getTime())
		calend.setTime(new java.util.Date())
		calend.add(Calendar.HOUR_OF_DAY, -1)
		params[0] = new Timestamp(calend.getTime().getTime())
		try {
			result = dbEngine.getJdbcUtil().queryForList(selectSql1, params, types);
			for (int i = 0; i < result.size(); i++) {
				Map<String, Object> res = result.get(i)
				String GROUP_ID = res.get("GROUP_ID")
				groupIdList.add(GROUP_ID)
			}
		} catch (Exception e) {
			// TODO: handle exception
			logger.error(this,e)
		}
		for(int i = 0; i < groupIdList.size(); i++){
			try {
				List<Object> pmValueList = new ArrayList<Object>()
				List<String> pmNameList = new ArrayList<String>()
				List<Object> insertValueList = new ArrayList<Object>()
				List<String> insertFeildList = new ArrayList<String>()
				Object[] param = new Object[1]
				int[] type = new int[1]
				type[0] = Types.VARCHAR
				param[0] = groupIdList[i]
				result = dbEngine.getJdbcUtil().queryForList(selectSql2, param, type);
				if(result != null && result.size()>0){
					Map<String, Object> res = result.get(0)
					insertValueList.add(res.get("BEGINTIME"))
					insertValueList.add(res.get("ENDTIME"))
					insertValueList.add(res.get("INFOMODELREFERENCED"))
					insertValueList.add(res.get("DNPREFIX"))
					insertValueList.add(res.get("SENDERNAME"))
					insertValueList.add(res.get("VENDORNAME"))
					insertValueList.add(res.get("JOBID"))
					insertValueList.add(res.get("OBJECTTYPE"))
					insertValueList.add(res.get("PM_DN"))
					insertValueList.add(res.get("PM_USERLABEL"))
					insertFeildList.add("START_TIME")
					insertFeildList.add("END_TIME")
					insertFeildList.add("INFOMODELREFERENCED")
					insertFeildList.add("DNPREFIX")
					insertFeildList.add("SENDERNAME")
					insertFeildList.add("VENDORNAME")
					insertFeildList.add("JOBID")
					insertFeildList.add("OBJECTTYPE")
					insertFeildList.add("PM_DN")
					insertFeildList.add("PM_USERLABEL")
					for (int j = 0; j < result.size(); j++) {
						res = result.get(j)
						pmValueList.add(res.get("PM_VALUE"))
						pmNameList.add(res.get("PM_NAME"))
					}
					for(int j = 0; j < pmValueList.size(); j++){
						String fd = pmNameList[j]
						String feild = ""
						Set set=dstDbFeildAndComment.entrySet()
						Iterator iter=set.iterator()
						while(iter.hasNext()){
							Map.Entry<String, String> enter=(Entry<String, String>) iter.next();
							String value = enter.getValue()
							String[] comment = value.split("\\s*,\\s*")
							if(comment[0].toLowerCase().trim().equals(pmNameList[j].toLowerCase().trim())) {
								feild=enter.getKey();
								break
							}
						}
						if(feild.equals("")){
							logger.debug("指标："+fd+"字段找不到,忽略")
							continue
						}
						insertFeildList.add(feild)
						insertValueList.add(pmValueList[j])
					}
					String insertSql = DbEngine.makeSql(insertFeildList, tableName)
					Object[] p = new Object[insertFeildList.size()]
					int[] t = new int[insertFeildList.size()]
					t[0] = Types.TIMESTAMP
					t[1] = Types.TIMESTAMP
					println insertValueList.size()
					for(int m = 0; m < insertValueList.size(); m++){
						p[m] = insertValueList[m]
					}
					for(int m = 2; m < insertValueList.size(); m++){
						t[m] = Types.VARCHAR
					}
					dbEngine.getJdbcUtil().update(insertSql, p, t)
				}
			} catch (Exception e) {
				// TODO: handle exception
				logger.error(this,e)
			}
		}
	}
}


